-- 1.用函数实现：求某个专业选修了某门课程的学生人数，并调用函数求出计算机系“数据库”课程的选课人数
USE "XSGL"
CREATE FUNCTION "求某个专业选修了某门课程的学生人数"(
                                       @专业 AS CHAR(16), @课程名 AS CHAR(10)
                                   )
    RETURNS INT
AS
BEGIN
    DECLARE @人数 INT
    SELECT @人数 = COUNT(DISTINCT "XS"."学号")
        FROM "CJ" INNER JOIN "XS" ON "XS"."学号" = "CJ"."学号"
                  INNER JOIN "KC" ON "KC"."课程号" = "CJ"."课程号"
        WHERE "XS"."专业" = @专业
          AND "KC"."课程名" = @课程名
    RETURN @人数
END
GO
SELECT "DBO"."求某个专业选修了某门课程的学生人数"('计算机', '数据库');
SELECT "DBO"."求某个专业选修了某门课程的学生人数"('计算机', '操作系统');
-- 2.用内嵌表值函数实现：查询某个专业所有学生所选的每门课的平均成绩；调用该函数求出计算机系的所有课程的平均成绩。
USE "XSGL"
CREATE FUNCTION "查询某个专业所有学生所选的每门课的平均成绩"(
    @专业 AS CHAR(16)
                                       )
    RETURNS TABLE
        AS
        RETURN
            (
                SELECT ISNULL(AVG("CJ"."成绩"), 0) AS "平均成绩", "CJ"."课程号"
                    FROM "CJ" INNER JOIN "XS" ON "XS"."学号" = "CJ"."学号"
                    WHERE "XS"."专业" = @专业
                    GROUP BY "CJ"."课程号"
            )
GO
SELECT *
    FROM "DBO"."查询某个专业所有学生所选的每门课的平均成绩"('计算机');
-- 3.创建多语句表值函数，通过学号作为实参调用该函数，可显示该学生的姓名以及各门课的成绩和学分，调用该函数求出“200515002”的各门课成绩和学分。
USE "XSGL"
CREATE FUNCTION "通过学号作为实参调用该函数，可显示该学生的姓名以及各门课的成绩和学分"(
    @学号 AS CHAR(10)
                                                    )
    RETURNS @表名变量 TABLE
                  (
                      "姓名"  NCHAR(10),
                      "成绩"  NUMERIC,
                      "课程号" CHAR(10),
                      "学分"  SMALLINT
                  )
AS
BEGIN
    INSERT @表名变量
    SELECT "XS"."姓名", "CJ"."成绩", "CJ"."课程号", "KC"."学分"
        FROM "XS" INNER JOIN "CJ" ON "CJ"."学号" = "XS"."学号"
                  INNER JOIN "KC" ON "KC"."课程号" = "CJ"."课程号"
    RETURN
END
GO
SELECT *
    FROM "DBO"."通过学号作为实参调用该函数，可显示该学生的姓名以及各门课的成绩和学分"('200515002');
-- 4.编写一个存储过程，统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，其输入参数是课程号，输出的是各级别人数及及格率，及格率的形式是90.25%，执行存储过程，在消息区显示1号课程的统计信息。
USE "XSGL"
CREATE PROCEDURE "统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，其输入参数是课程号" @课程号 CHAR(10)
AS
SELECT COUNT(CASE WHEN "成绩" >= 90 THEN 1 END)                                                                                                                              AS "优秀",
       COUNT(CASE WHEN "成绩" < 90 AND "成绩" >= 80 THEN 1 END)                                                                                                                AS "良好",
       COUNT(CASE WHEN "成绩" >= 70 AND "成绩" < 79 THEN 1 END)                                                                                                                AS "中等",
       COUNT(CASE WHEN "成绩" >= 60 AND "成绩" < 70 THEN 1 END)                                                                                                                AS "及格",
       CONCAT(CASE WHEN COUNT(DISTINCT "学号") = 0 THEN 0 ELSE CONVERT(FLOAT, COUNT(CASE WHEN "成绩" >= 60 THEN 1 END)) / CONVERT(FLOAT, COUNT(DISTINCT "学号")) * 100 END, '%') AS "及格率"
    FROM "CJ"
    WHERE "CJ"."课程号" = @课程号
GO
EXEC "统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，其输入参数是课程号" "J003"
-- 5.创建一个带有输入参数的存储过程，该存储过程根据传入的学生名字，查询其选修的课程名和成绩，执行存储过程，在消息区显示方可以同学的相关信息。
CREATE PROCEDURE "该存储过程根据传入的学生名字，查询其选修的课程名和成绩" @姓名 NCHAR(10) AS
SELECT "KC"."课程名", "CJ"."成绩"
    FROM "XS" INNER JOIN "CJ" ON "CJ"."学号" = "XS"."学号"
              INNER JOIN "KC" ON "KC"."课程号" = "CJ"."课程号"
    WHERE "XS"."姓名" = @姓名
GO
EXEC "该存储过程根据传入的学生名字，查询其选修的课程名和成绩""王林"
-- 6.以基本表 课程和选课表为基础，完成如下操作 生成显示如下报表形式的游标：报表首先列出学生的学号和姓名，然后在此学生下，列出其所选的全部课程的课程号、课程名和学分；依此类推，直到列出全部学生。
USE "XSGL"
DECLARE @学号 CHAR(10),@姓名 NCHAR(10)
DECLARE "CUR_XS" CURSOR FOR SELECT "学号", "姓名"
                                FROM "XS"
OPEN "CUR_XS"
FETCH NEXT FROM "CUR_XS" INTO @学号,@姓名
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @学号 + @姓名;
        DECLARE @课程号 CHAR(10),@课程名 CHAR(16),@学分 SMALLINT;
        DECLARE "CUR_CJ" CURSOR FOR SELECT "CJ"."课程号", "KC"."课程名", "KC"."学分"
                                        FROM "CJ" INNER JOIN "KC" ON "KC"."课程号" = "CJ"."课程号"
                                        WHERE "CJ"."学号" = @学号
        OPEN "CUR_CJ"
        FETCH NEXT FROM "CUR_CJ" INTO @课程号,@课程名,@学分
        WHILE @@FETCH_STATUS = 0
            BEGIN
                PRINT @课程号 + @课程名 + CONVERT(CHAR(10), @学分);
                FETCH NEXT FROM "CUR_CJ" INTO @课程号,@课程名,@学分
            END
        CLOSE "CUR_CJ"
        DEALLOCATE "CUR_CJ";
        FETCH NEXT FROM "CUR_XS" INTO @学号,@姓名
    END
CLOSE "CUR_XS"
DEALLOCATE "CUR_XS"
-- 7.请设计一个存储过程实现下列功能：判断某个专业某门课程成绩排名为n的学生的成绩是否低于该门课程的平均分，如果低于平均分，则将其成绩改为平均分，否则输出学号、姓名、班号、课程号、课程名、成绩。（提示：可以在存储过程内部使用游标）。
CREATE PROCEDURE "判断某个专业某门课程成绩排名为N的学生的成绩是否低于该门课程的平均分，如果低于平均分，则将其成绩改为平均分，否则输出学号、姓名、课程号、课程名、成绩。"
    @专业 CHAR(16), @课程名 CHAR(16), @N INT
AS
BEGIN
    DECLARE
        "CUR" CURSOR SCROLL FOR SELECT "XS"."学号", "XS"."姓名", "KC"."课程号", "CJ"."成绩"
                                    FROM "XS" INNER JOIN "CJ" ON "CJ"."学号" = "XS"."学号"
                                              INNER JOIN "KC" ON "KC"."课程号" = "CJ"."课程号"
                                    WHERE "XS"."专业" = @专业
                                      AND "KC"."课程名" = @课程名
    DECLARE @学号 CHAR(10), @姓名 NCHAR(10), @课程号 CHAR(10), @成绩 NUMERIC
    DECLARE @平均分 INT
    SELECT @平均分 = AVG("CJ"."成绩")
        FROM "XS" INNER JOIN "CJ" ON "CJ"."学号" = "XS"."学号"
                  INNER JOIN "KC" ON "KC"."课程号" = "CJ"."课程号"
        WHERE "XS"."专业" = @专业
          AND "KC"."课程名" = @课程名
    OPEN "CUR"
    FETCH ABSOLUTE @N FROM "CUR" INTO @学号,@姓名,@课程号,@成绩
    IF (@成绩 > @平均分)
            PRINT @学号 + @姓名 + @课程号 + @课程名 + CONVERT(CHAR(6), @成绩)
        ELSE
            UPDATE "CJ"
            SET "CJ"."成绩"=@平均分
                WHERE "CJ"."学号" = @学号
                  AND "CJ"."课程号" = @课程号
    CLOSE "CUR"
    DEALLOCATE "CUR";
END
GO
EXEC "判断某个专业某门课程成绩排名为N的学生的成绩是否低于该门课程的平均分，如果低于平均分，则将其成绩改为平均分，否则输出学号、姓名、课程号、课程名、成绩。" '计算机', '英语', 1
-- 8.对xsgl数据库设计存储过程，设计程序实现更新某门课程的成绩，将该门课程成绩低于课程平均成绩的学生成绩都加上3分。
CREATE PROCEDURE "设计程序实现更新某门课程的成绩，将该门课程成绩低于课程平均成绩的学生成绩都加上3分。" @课程名 CHAR(16)
AS
BEGIN
    DECLARE @课程号 CHAR(10);
    SELECT @课程号 = "课程号"
        FROM "KC"
        WHERE "KC"."课程名" = @课程名;
    UPDATE "CJ"
    SET "CJ"."成绩"="CJ"."成绩" + 3
        WHERE "CJ"."课程号" = @课程号
          AND "CJ"."成绩" < (
                              SELECT AVG("成绩")
                                  FROM "CJ"
                                  WHERE "CJ"."课程号" = @课程号
                          );
END
GO
EXEC "设计程序实现更新某门课程的成绩，将该门课程成绩低于课程平均成绩的学生成绩都加上3分。" '操作系统'
-- 9.针对实验六创建的借书表：lendt(bno（索书号）,sno（学号）,ldate（借阅日期）,rdate（应还日期），relend（是否续借）),
-- 再创建还书表 return(bno（索书号）,sno（学号），rrdate(还书日期), cq(是否超期), fakuan（罚款金额），还书日期的默认值也是当前日期，是否超期默认值为否；
CREATE TABLE "RETURN"
(
    "BO"     CHAR(20),
    "SNO"    CHAR(10),
    "RRDATE" DATETIME DEFAULT GETDATE(),
    "CQ"     NCHAR(2) DEFAULT '否',
    "FAKUAN" NUMERIC,
)
-- 设计存储过程实现还书功能，某个学生还某本图书，首先删除相应的借阅记录，然后插入一条还书记录，如果超期则将是否超期改为是，并且按照每天0.5元计算罚款，并将罚款金额记录。
CREATE PROCEDURE "还书"
    @BO CHAR(20), @SNO CHAR(10)
AS
BEGIN
    DECLARE @RDATE DATETIME
    SELECT @RDATE = "RDATE"
        FROM "LENDT"
        WHERE "BO" = @BO
          AND "SNO" = @SNO
    DELETE "LENDT"
        WHERE "BO" = @BO
          AND "SNO" = @SNO
    DECLARE @CQ NCHAR(2),@FAKUAN NUMERIC
    IF @RDATE < GETDATE()
            BEGIN
                SET @CQ = '是'
                SET @FAKUAN = DATEDIFF(DD, @RDATE, GETDATE()) * 0.5
            END
        ELSE
            BEGIN
                SET @CQ = '否'
                SET @FAKUAN = 0
            END
    INSERT
        INTO "RETURN"("BO", "SNO", "RRDATE", "CQ", "FAKUAN")
        VALUES
            (@BO, @SNO, GETDATE(), @CQ, @FAKUAN)
END
GO
INSERT
    INTO "XSGL"."DBO"."LENDT"
        ("BO", "SNO", "LDATE", "RDATE", "RELEND")
    VALUES
        (N'111', N'222', DEFAULT, DEFAULT, DEFAULT)
EXEC "还书" '111', '222'
-- 10. 使用存储过程实现转专业功能，将某个学生（学号）转入到某个专业中，如果想转入的专业是计算机专业那么要求该学生的平均成绩必须超过95分，否则不允许转专业，并将转专业的信息插入到一个转专业的表里，changesd(学号，原专业，新专业，平均成绩)
CREATE TABLE "CHANGESD"
(
    "学号"   CHAR(10),
    "原专业"  CHAR(16),
    "新专业"  CHAR(16),
    "平均成绩" NUMERIC,
)
CREATE PROCEDURE "转专业"
    @学号 CHAR(10), @新专业 CHAR(16)
AS
BEGIN
    DECLARE @原专业 CHAR(16), @平均成绩 NUMERIC
    SELECT @原专业 = "XS"."专业", @平均成绩 = AVG("CJ"."成绩")
        FROM "XS" INNER JOIN "CJ" ON "CJ"."学号" = "XS"."学号"
        WHERE "XS"."学号" = @学号
        GROUP BY "XS"."专业"
    IF @原专业 <> @新专业 AND (@新专业 <> '计算机' OR @平均成绩 > 95)
            BEGIN
                UPDATE "XS"
                SET "专业"=@新专业
                    WHERE "学号" = @学号
                INSERT "CHANGESD"("学号", "原专业", "新专业", "平均成绩")
                    VALUES
                        (@学号, @原专业, @新专业, @平均成绩)
            END
END
GO
EXEC "转专业" '2006030102','信息管理'
EXEC "转专业" '2006030102','计算机'